package com.infodms.dms.dao.sales.dealer;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.infodms.dms.common.Constant;
import com.infodms.dms.common.Utility;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.dao.productmanage.ProductManageDao;
import com.infodms.dms.po.FsFileuploadPO;
import com.infodms.dms.po.TmOrgPO;
import com.infodms.dms.util.StringUtil;
import com.infoservice.po3.POFactory;
import com.infoservice.po3.POFactoryBuilder;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;
import com.infoservice.po3.core.callback.DAOCallback;

/**
 * @Title: CHANADMS
 *
 * @Description:
 *
 * @Copyright: Copyright (c) 2010
 *
 * @Company:  www.infoservice.com.cn
 * @Date: 2010-7-5
 *
 * @author zjy 
 * @mail   zhaojinyu@infoservice.com.cn
 * @version 1.0
 * @remark 
 */
public class DealerInfoDao extends BaseDao<PO> {
	public static Logger logger = Logger.getLogger(DealerInfoDao.class);
	private static POFactory factory = POFactoryBuilder.getInstance();
	private static final DealerInfoDao dao = new DealerInfoDao ();
	public static final DealerInfoDao getInstance() {
		return dao;
	}
	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}
	/**
	 * Function         : 经销商查询
	 * @param           : 经销商CODE
	 * @param           : 经销商名称
	 * @param           : 经销商等级
	 * @param           : 经销商状态
	 * @param           : 上级经销商代码
	 * @param           : 上级车厂组织
	 * @param           : 经销商类型
	 * @param           : 经销商公司ID
	 * @param           : 分页参数
	 * @return          : 经销商信息
	 * @throws          : Exception
	 * LastUpdate       : 2010-7-5
	 */
	public PageResult<Map<String, Object>> queryDealerInfo(String dealerClass,String dealerCode,String dealerName,String dealerLevel,String status,String sJdealerCode,String orgCode,String dealerType,String companyId,String oemCompanyId,String province,int curPage,int pageSize) throws Exception{
		StringBuffer sql= new StringBuffer();
		/*sql.append("SELECT D.DEALER_ID,\n");
		sql.append("       D.DEALER_CODE,\n");  
		sql.append("       D.DEALER_SHORTNAME,\n");  
		sql.append("       D.DEALER_TYPE,\n");  
		sql.append("       D.STATUS,\n"); 
		sql.append("       D.DEALER_LEVEL,\n"); 
		sql.append("       DECODE(TD1.DEALER_SHORTNAME, NULL, '', TD1.DEALER_SHORTNAME) AS SHANGJINAME,\n"); 
		if(String.valueOf(Constant.DEALER_LEVEL_01).equals(dealerLevel))
		{
			sql.append("      D.SHANGJIORGNAME,\n");  	
		}else
		{
			sql.append("        '' AS SHANGJIORGNAME,\n");
		}
		sql.append("       D.COMPANY_SHORTNAME\n");
		sql.append("  FROM (SELECT TD.DEALER_ID,\n");  
		sql.append("               TD.DEALER_CODE,\n");  
		sql.append("               TD.DEALER_SHORTNAME,\n");  
		sql.append("               TD.DEALER_TYPE,\n");  
		sql.append("               TD.STATUS,\n");
		sql.append("               TD.DEALER_LEVEL,\n"); 
		if(String.valueOf(Constant.DEALER_LEVEL_01).equals(dealerLevel))
		{
			sql.append("               TMO.ORG_NAME AS SHANGJIORGNAME,\n"); 
		}
		sql.append("               TD.PARENT_DEALER_D,\n");  
		sql.append("               TC.COMPANY_SHORTNAME\n");  
		if(String.valueOf(Constant.DEALER_LEVEL_01).equals(dealerLevel))
		{
			sql.append("           FROM TM_DEALER TD, TM_COMPANY TC,TM_ORG TMO,TM_DEALER_ORG_RELATION TDOR\n");
		}else
		{
			sql.append("           FROM TM_DEALER TD, TM_COMPANY TC \n");
		}
		sql.append("         WHERE TD.COMPANY_ID = TC.COMPANY_ID\n");  
		if(!"".equals(dealerCode)&&dealerCode!=null)
		{
			sql.append("           AND TD.DEALER_CODE LIKE '%"+dealerCode+"%'\n"); 
		}
		if(!"".equals(dealerName)&&dealerName!=null)
		{
			sql.append("           AND TD.DEALER_SHORTNAME LIKE '%"+dealerName+"%'\n"); 
		}
		if(!"".equals(status)&&status!=null)
		{
			sql.append("           AND TD.STATUS = "+status+"\n"); 
		}
		if(!"".equals(dealerType)&&dealerType!=null)
		{
			sql.append("           AND TD.DEALER_TYPE = "+dealerType+"\n"); 
		}
		if(String.valueOf(Constant.DEALER_LEVEL_01).equals(dealerLevel))
		{
			sql.append("           AND TD.DEALER_ID = TDOR.DEALER_ID\n"); 
			sql.append("           AND TDOR.ORG_ID = TMO.ORG_ID\n"); 
			sql.append("           AND TD.DEALER_LEVEL = "+dealerLevel+"\n"); 
			if(!"".equals(orgCode)&&orgCode!=null)
			{
				sql.append("     AND EXISTS (SELECT 1 FROM TM_DEALER_ORG_RELATION TDOR,TM_ORG TMO WHERE TDOR.ORG_ID=TMO.ORG_ID AND TMO.ORG_CODE='"+orgCode+"' AND TDOR.DEALER_ID=TD.DEALER_ID)\n"); 
			}
		}else
		{
			sql.append("           AND TD.DEALER_LEVEL = "+dealerLevel+"\n"); 
			if(!"".equals(sJdealerCode)&&sJdealerCode!=null)
			{
				sql.append("           AND TD.PARENT_DEALER_D = (SELECT DEALER_ID FROM TM_DEALER WHERE DEALER_CODE ='"+sJdealerCode+"')\n");	
			}
		} 
		if(!"".equals(companyId)&&companyId!=null)
		{
			sql.append("           AND TD.COMPANY_ID = "+companyId+"\n"); 
		}
		if(oemCompanyId!=null&&!"".equals(oemCompanyId))
		{
			sql.append("           AND TD.OEM_COMPANY_ID = "+oemCompanyId+"\n"); 
		}
		if(dealerClass!=null&&dealerClass!=""){
			sql.append("          AND TD.DEALER_CLASS='"+dealerClass+"'\n"); 
		}
		sql.append("           AND TD.COMPANY_ID = TC.COMPANY_ID)D\n");
		sql.append("  LEFT JOIN TM_DEALER TD1 ON D.PARENT_DEALER_D = TD1.DEALER_ID\n");
		sql.append("  ORDER BY D.DEALER_ID ASC\n");*/
		
		
		sql.append("SELECT D.DEALER_ID,\n");
		sql.append("       D.DEALER_CODE,\n");  
		sql.append("       D.DEALER_SHORTNAME,\n");  
		sql.append("       D.DEALER_TYPE,\n");  
		sql.append("       D.STATUS,\n"); 
		sql.append("       D.DEALER_LEVEL,\n"); 
		sql.append("       DECODE(TD1.DEALER_SHORTNAME, NULL, '', TD1.DEALER_SHORTNAME) AS SHANGJINAME,\n"); 
		sql.append("       D.SHANGJIORGNAME,\n");  	
		sql.append("       D.COMPANY_SHORTNAME,\n");
		sql.append("       TO_CHAR(E.CREATE_DATE,'yyyy-MM-dd') AS CREATEDATE,\n");
		sql.append("       TO_CHAR(D.UPDATE_DATE,'yyyy-MM-dd') AS UPDATEDATE,\n");
		sql.append("       DECODE(E.NAME, NULL, '', E.NAME) AS CREATEPER,\n");
		sql.append("       DECODE(F.NAME, NULL, '', F.NAME) AS UPDATEPER\n");
		sql.append("  FROM (SELECT TD.DEALER_ID,\n");  
		sql.append("               TD.DEALER_CODE,\n");  
		sql.append("               TD.DEALER_SHORTNAME,\n");  
		sql.append("               TD.DEALER_TYPE,\n");  
		sql.append("               TD.STATUS,\n");
		sql.append("               TD.DEALER_LEVEL,\n"); 
		sql.append("               TD.CREATE_BY,\n"); 
		sql.append("               TD.UPDATE_BY,\n"); 
		sql.append("               TD.CREATE_DATE,\n"); 
		sql.append("               TD.UPDATE_DATE,\n"); 
		sql.append("               TMO.ORG_NAME AS SHANGJIORGNAME,\n"); 
		sql.append("               TD.PARENT_DEALER_D,\n");  
		sql.append("               TC.COMPANY_SHORTNAME\n");  
		sql.append("           FROM TM_DEALER TD, TM_COMPANY TC,TM_ORG TMO,TM_DEALER_ORG_RELATION TDOR\n");
		sql.append("         	WHERE TD.COMPANY_ID = TC.COMPANY_ID\n");  
		if(dealerCode!=null&&!"".equals(dealerCode))
		{
			sql.append("           AND TD.DEALER_CODE LIKE '%"+dealerCode+"%'\n"); 
		}
		if(dealerName!=null&&!"".equals(dealerName))
		{
			sql.append("           AND TD.DEALER_SHORTNAME LIKE '%"+dealerName+"%'\n"); 
		}
		if(status!=null&&!"".equals(status))
		{
			sql.append("           AND TD.STATUS = "+status+"\n"); 
		}
		if(dealerType!=null&&!"".equals(dealerType))
		{
			sql.append("           AND TD.DEALER_TYPE = "+dealerType+"\n"); 
		}
		sql.append("           AND F_GET_PID(TD.DEALER_ID) = TDOR.DEALER_ID \n"); 
		sql.append("           AND TDOR.ORG_ID = TMO.ORG_ID\n"); 
		if(dealerLevel!=null&&!"".equals(dealerLevel))
		{
			sql.append("           AND TD.DEALER_LEVEL = "+dealerLevel+"\n"); 
		}
		if(orgCode!=null&&!"".equals(orgCode))
		{
			sql.append("     AND EXISTS (SELECT 1 FROM TM_DEALER_ORG_RELATION TDOR,TM_ORG TMO WHERE TDOR.ORG_ID=TMO.ORG_ID AND TMO.ORG_CODE='"+orgCode+"' AND (TDOR.DEALER_ID=TD.DEALER_ID OR TDOR.DEALER_ID=TD.PARENT_DEALER_D))\n"); 
		}
	
		
		if(sJdealerCode!=null&&!"".equals(sJdealerCode))
		{
			sql.append("           AND TD.PARENT_DEALER_D = (SELECT DEALER_ID FROM TM_DEALER WHERE DEALER_CODE ='"+sJdealerCode+"')\n");	
		}
		if(companyId!=null&&!"".equals(companyId))
		{
			sql.append("           AND TD.COMPANY_ID = "+companyId+"\n"); 
		}
		if(oemCompanyId!=null&&!"".equals(oemCompanyId))
		{
			sql.append("           AND TD.OEM_COMPANY_ID = "+oemCompanyId+"\n"); 
		}
		if(dealerClass!=null&&dealerClass!=""){
			sql.append("          AND TD.DEALER_CLASS='"+dealerClass+"'\n"); 
		}
		if(province!=null&&province!=""){
			sql.append("          AND TD.PROVINCE_ID='"+province+"'\n"); 
		}
		
		sql.append("           AND TD.COMPANY_ID = TC.COMPANY_ID)D\n");
		sql.append("  LEFT JOIN TM_DEALER TD1 ON D.PARENT_DEALER_D = TD1.DEALER_ID\n");
		sql.append("  LEFT JOIN TC_USER E ON D.CREATE_BY=E.USER_ID\n");
		sql.append("  LEFT JOIN TC_USER F ON D.UPDATE_BY=F.USER_ID\n");
		sql.append("  ORDER BY D.STATUS ASC, D.DEALER_ID ASC\n");
		PageResult<Map<String, Object>> rs = dao.pageQuery(sql.toString(), null, getFunName(),pageSize, curPage);
		return rs;
	}
	/**
	 * 新增经销商时查询经销商的公司的组织Id
	 * 
	 * @param companyId 公司Id
	 * @return
	 * @throws Exception
	 */
	public  List<TmOrgPO> getOrgInfo(Long companyId)throws Exception {

		String query = " SELECT TMO.ORG_ID FROM TM_ORG TMO WHERE TMO.COMPANY_ID = "+companyId;

		logger.debug("SQL+++++++++++++++++++++++: " + query);
		return factory.select(query, null, new DAOCallback<TmOrgPO>() {
			public TmOrgPO wrapper(ResultSet rs, int idx) {
				TmOrgPO bean = new TmOrgPO();
				try {
					bean.setOrgId(rs.getLong("ORG_ID"));
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return bean;
			}
		});
	}
	/**
	 * 根据经销商Id查询经销商详情
	 * @param dealerId
	 * @return
	 */
	public Map<String, Object> getDealerInfobyId(String dealerId){
		StringBuffer sql= new StringBuffer();
		sql.append("SELECT D.*,\n");
		sql.append("       TD.DEALER_ID AS SJDEALERID,\n");  
		sql.append("       TD.DEALER_CODE AS SJDEALERCODE,\n");  
		sql.append("       DECODE(D.DEALER_LEVEL,\n");  
		sql.append("              10851001,\n");  
		sql.append("              (SELECT TMO.ORG_ID\n");  
		sql.append("                 FROM TM_ORG TMO, TM_DEALER_ORG_RELATION TDOR\n");  
		sql.append("                WHERE TMO.ORG_ID = TDOR.ORG_ID\n");  
		sql.append("                  AND TDOR.DEALER_ID = D.DEALER_ID),\n");  
		sql.append("              '') AS ORG_ID,\n");  
		sql.append("       DECODE(D.DEALER_LEVEL,\n");  
		sql.append("              10851001,\n");  
		sql.append("              (SELECT TMO.ORG_CODE\n");  
		sql.append("                 FROM TM_ORG TMO, TM_DEALER_ORG_RELATION TDOR\n");  
		sql.append("                WHERE TMO.ORG_ID = TDOR.ORG_ID\n");  
		sql.append("                  AND TDOR.DEALER_ID = D.DEALER_ID),\n");  
		sql.append("              '') AS ORG_CODE\n");  
		sql.append("  FROM (SELECT TD.DEALER_ID,\n");  
		sql.append("               TD.COMPANY_ID,\n");  
		sql.append("               TD.DEALER_TYPE,\n");  
		sql.append("               TD.DEALER_CODE,\n");  
		sql.append("               TD.DEALER_NAME,\n");  
		sql.append("               TD.DEALER_CLASS,\n"); 
		sql.append("               TD.STATUS,\n");  
		sql.append("               TD.DEALER_LEVEL,\n");  
		sql.append("               TD.PARENT_DEALER_D,\n");  
		sql.append("               TD.DEALER_ORG_ID,\n");  
		sql.append("               TD.PROVINCE_ID,\n");  
		sql.append("               TD.CITY_ID,\n");  
		sql.append("               TD.ZIP_CODE,\n");  
		sql.append("               TD.ADDRESS,\n");  
		sql.append("               TD.PHONE,\n");  
		sql.append("               TD.FAX_NO,\n");  
		sql.append("               TD.LINK_MAN,\n");  
		sql.append("               TD.EMAIL,\n");  
		sql.append("               TD.REMARK,\n");  
		sql.append("               TD.DEALER_SHORTNAME,\n");  
		sql.append("               TD.OEM_COMPANY_ID,\n");  
		sql.append("               TD.PRICE_ID,\n");   
		sql.append("               TC.COMPANY_SHORTNAME,\n");  
		sql.append("               TD.ERP_CODE,\n");  
		sql.append("               TD.TAXES_NO,td.dealer_labour_type,\n");  
		sql.append("               TD.IS_DQV,\n"); 
		sql.append("               TD.BALANCE_LEVEL,\n"); 
		sql.append("               TD.INVOICE_LEVEL,TD.COUNTIES,TD.TOWNSHIP,TD.LEGAL,TD.WEBMASTER_PHONE,TD.DUTY_PHONE,TD.BANK,TD.MAIN_RESOURCES,TD.ADMIN_LEVEL,TD.IMAGE_LEVEL,TD.TAX_LEVEL\n"); 
		sql.append("          FROM TM_DEALER TD, TM_COMPANY TC\n");  
		sql.append("         WHERE TD.COMPANY_ID = TC.COMPANY_ID\n");  
		sql.append("           AND TD.DEALER_ID = "+dealerId+") D\n");  
		sql.append("  LEFT JOIN TM_DEALER TD ON D.PARENT_DEALER_D = TD.DEALER_ID\n");
		logger.debug("SQL+++++++++++++++++++++++: " + sql.toString());
		Map<String, Object> map = pageQueryMap(sql.toString(),null,getFunName());
		return map;
	}
	/**
	 * 根据地址Id查询经销商地址详情
	 * @param id
	 * @return
	 */
	public Map<String, Object> getAddressfobyId(String id){
		StringBuffer sql= new StringBuffer();
		sql.append("SELECT TVA.Receive_Org,TVA.Province_Id,TVA.City_Id,TVA.Area_Id,TVA.ID,TVA.ADD_CODE,TVA.ADDRESS,TVA.LINK_MAN,TVA.TEL,TVA.REMARK,TVA.STATUS,TVA.DEALER_ID, TD.address as myaddress  FROM TM_VS_ADDRESS TVA,TM_DEALER TD WHERE TVA.Dealer_Id=TD.DEALER_ID  and TVA.ID ='"+id+"'");
		logger.debug("SQL+++++++++++++++++++++++: " + sql.toString());
		Map<String, Object> map = pageQueryMap(sql.toString(),null,getFunName());
		return map;
	}
	public  int setDownDealerPrice(String erpCode)throws Exception {
		StringBuffer sql= new StringBuffer();
		sql.append("UPDATE t_dealer_account_details_tmp TDADT SET TDADT.REMARK='',TDADT.EXPORT_STATUS=0\n");
		sql.append("WHERE TDADT.REMARK LIKE '%XXX%' AND TDADT.EXPORT_STATUS=3  AND TDADT.DEALER_ERP_ORG_CODE='"+erpCode+"'");
		return factory.update(sql.toString(), null);
		
	}
	/**
	 * 查询经销商业务范围
	 * 
	 * @param dealerId 经销商Id
	 * @return
	 * @throws Exception
	 */
	public  List<Map<String, Object>> getDealerBusinessArea(String dealerId)throws Exception {
		StringBuffer sql= new StringBuffer();
		sql.append("SELECT TDBA.RELATION_ID,\n");
		sql.append("       TDBA.DEALER_ID,\n");  
		sql.append("       TDBA.AREA_ID,\n");  
		sql.append("       TBA.AREA_CODE,\n");  
		sql.append("       TBA.AREA_NAME\n");  
		sql.append("  FROM TM_DEALER_BUSINESS_AREA TDBA, TM_BUSINESS_AREA TBA\n");  
		sql.append(" WHERE TDBA.AREA_ID = TBA.AREA_ID\n");  
		sql.append("   AND TDBA.DEALER_ID = "+dealerId+"\n");
		logger.debug("SQL+++++++++++++++++++++++: " + sql.toString());
		return pageQuery(sql.toString(),null,getFunName());
	}
	/**
	 * 查询经销商地址
	 * 
	 * @param dealerId 经销商Id
	 * @return
	 * @throws Exception
	 */
	public  List<Map<String, Object>> getAddress(String dealerId)throws Exception {

		StringBuffer sql= new StringBuffer();
		sql.append("SELECT TVA.ID,\n");  
		sql.append("       TVA.ADD_CODE,\n");  
		sql.append("       TVA.ADDRESS,\n");  
		sql.append("       TVA.LINK_MAN,\n");  
		sql.append("       TVA.TEL,\n");  
		sql.append("       TVA.REMARK,\n");  
		sql.append("       TVA.STATUS,\n");  
		sql.append("       TVA.DEALER_ID\n");  
		sql.append("  FROM TM_VS_ADDRESS TVA\n");  
		sql.append(" WHERE TVA.DEALER_ID = "+dealerId+" ORDER BY TVA.Status,TVA.ADD_CODE,TVA.ADDRESS \n");		
		logger.debug("SQL+++++++++++++++++++++++: " + sql.toString());
		return pageQuery(sql.toString(),null,getFunName());
	}
	/**
	 * 查询没维护给经销商业务范围
	 * 
	 * @param dealerId 经销商Id
	 * @param companyId 公司Id
	 * @return
	 * @throws Exception
	 */
	public  List<Map<String, Object>> getBusinessNoInDealer(String dealerId,Long companyId)throws Exception {

		StringBuffer sql= new StringBuffer();
		sql.append("SELECT TBA.AREA_ID, TBA.AREA_CODE, TBA.AREA_NAME\n");
		sql.append("  FROM TM_BUSINESS_AREA TBA\n");  
		sql.append(" WHERE TBA.COMPANY_ID = "+companyId+"\n");  
		sql.append("   AND NOT EXISTS (SELECT 1\n");  
		sql.append("          FROM TM_DEALER_BUSINESS_AREA TDBA\n");  
		sql.append("         WHERE TDBA.AREA_ID = TBA.AREA_ID\n");  
		sql.append("           AND TDBA.DEALER_ID = "+dealerId+")\n");

		//增加 隐藏同一公司下已经被其他渠道对应的业务范围 的逻辑 by WangLiang @100727
		//也就是说不能一个业务范围不能被同一个公司内的经销商选择多次
		sql.append("   AND NOT EXISTS (SELECT 1\n");
		sql.append("       FROM TM_DEALER D, TM_DEALER_BUSINESS_AREA A\n");  
		sql.append("      WHERE D.COMPANY_ID IN\n");  
		sql.append("            (SELECT COMPANY_ID\n");  
		sql.append("               FROM TM_DEALER\n");  
		sql.append("              WHERE DEALER_ID = "+dealerId+")\n");  
		sql.append("        AND (D.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+" OR D.DEALER_TYPE = "+Constant.DEALER_TYPE_JSZX+")\n");  
		sql.append("        AND D.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("        AND A.DEALER_ID = D.DEALER_ID\n");  
		sql.append("        AND A.AREA_ID = TBA.AREA_ID)");
		//END
		
		logger.debug("SQL+++++++++++++++++++++++: " + sql.toString());
		return pageQuery(sql.toString(),null,getFunName());
	}
	
	/*
	 * 通过经销商ID查询经销商名称
	 * @dealerIds 经销商ID
	 */
	public List<Map<String,Object>> getDealerInfo(String dealerIds) {
		StringBuffer sql = new StringBuffer("") ;
		
		sql.append("select td.dealer_name, td.dealer_id,td.dealer_code\n");
		sql.append("  from tm_dealer td\n");  
		sql.append(" where td.dealer_id in ("+dealerIds+")\n");  
		sql.append("and td.status = "+Constant.STATUS_ENABLE+"\n");
		sql.append(" order by td.dealer_name,td.dealer_code\n");

		return dao.pageQuery(sql.toString(), null, getFunName()) ;
	}
	/*
	 * 通过经销商ID查询其下级经销商
	 * @dealerIds 经销商ID
	 */
	public List<Map<String,Object>> getLowDelaerInfo(String dealerIds) {
		StringBuffer sql = new StringBuffer("") ;
		
		sql.append("select td2.dealer_name, td2.dealer_id,td2.parent_dealer_d\n");
		sql.append("  from tm_dealer td1, tm_dealer td2\n");  
		sql.append(" where td2.parent_dealer_d = td1.dealer_id\n");  
		sql.append(" and td1.dealer_id in ("+dealerIds+")\n");  
		sql.append(" and td1.status = "+Constant.STATUS_ENABLE+"\n");  
		sql.append(" and td2.status = "+Constant.STATUS_ENABLE+"\n");  
		sql.append(" order by td2.dealer_name\n");

		return dao.pageQuery(sql.toString(), null, getFunName()) ;
	}
	/*
	 * 经销商查询
	 * @dealerId 本级经销商Id
	 * @dealerIds 上级经销商Id
	 * @dealerLevel 需查询经销商级别
	 * @dealerType 经销商类型
	 */
	public List<Map<String, Object>> getDel(String dealerId,String dealerIds, String dealerLevel, String dealerType) {
		StringBuffer sql = new StringBuffer("") ;
		
		sql.append("SELECT  TD.DEALER_NAME, \n") ;
		sql.append("		TD.DEALER_CODE, \n") ;
		sql.append("		TD.DEALER_ID \n") ;
		sql.append("FROM	TM_DEALER TD \n") ;
		sql.append("WHERE	TD.STATUS = "+ Constant.STATUS_ENABLE +" \n") ;
		
		if (!"".equals(dealerId)) {
			sql.append("	AND	TD.DEALER_ID IN ("+ dealerId +") \n") ;
		}
		if (!"".equals(dealerLevel)) {
			sql.append("	AND	TD.DEALER_LEVEL = "+ dealerLevel.trim() +" \n") ;
		}
		if (!"".equals(dealerIds)) {
			sql.append("	AND	TD.PARENT_DEALER_D IN ("+ dealerIds +") \n") ;
		}
		if (!"".equals(dealerType)) {
			sql.append("	AND	TD.DEALER_TYPE = "+ dealerType.trim() +" \n") ;
		}
		
		return dao.pageQuery(sql.toString(), null, getFunName()) ;
	}
	/*
	 * 查询经销商所属区域
	 * @dealerId 经销商Id
	 */
	public List<Map<String, Object>> getOrg(String dealerId) {
		StringBuffer sql = new StringBuffer("") ;
		
		sql.append("SELECT TMDOR.ORG_ID\n");
		sql.append("FROM TM_DEALER TMD, TM_DEALER_ORG_RELATION TMDOR\n");  
		sql.append("WHERE TMD.DEALER_ID = TMDOR.DEALER_ID\n");  
		sql.append("AND TMD.DEALER_ID = " + dealerId + "\n");

		return dao.pageQuery(sql.toString(), null, getFunName()) ;
	}
	
	public List<Map<String, Object>> getDel(String dealerId,String poseId) {
		StringBuffer sql = new StringBuffer("") ;
		
		sql.append("SELECT TMD.DEALER_NAME\n");
		sql.append("  FROM TM_DEALER               TMD,\n");  
		sql.append("       TM_DEALER_BUSINESS_AREA TMDBA,\n");  
		sql.append("       TC_POSE                 TCP,\n");  
		sql.append("       TM_POSE_BUSINESS_AREA   TMPBA\n");  
		sql.append(" WHERE 1 = 1\n");  
		sql.append("   AND TMD.DEALER_ID = TMDBA.DEALER_ID\n");  
		sql.append("   AND TMDBA.AREA_ID = TMPBA.AREA_ID\n");  
		sql.append("   AND TMPBA.POSE_ID = TCP.POSE_ID\n");  
		sql.append("   AND TMD.STATUS = " + Constant.STATUS_ENABLE + " \n");  
		sql.append("   AND TMD.DEALER_ID in (" + dealerId + ") \n");  
		sql.append("   AND TCP.POSE_ID = " + poseId + " \n");
		
		return dao.pageQuery(sql.toString(), null, getFunName()) ;
	}
	
	/**
	 * 获取当前经销商的下级经销商
	 * @param dealerId
	 * @return
	 */
	public List<Map<String, Object>> getDel(String dealerId) {
		StringBuffer sql = new StringBuffer("\n") ;
		
		sql.append("SELECT TMD2.DEALER_ID, TMD2.DEALER_NAME\n");
		sql.append("  FROM TM_DEALER TMD1, TM_DEALER TMD2\n");  
		sql.append(" WHERE TMD1.DEALER_ID = TMD2.PARENT_DEALER_D\n");  
		sql.append("   AND TMD1.STATUS = " + Constant.STATUS_ENABLE + "\n");  
		sql.append("   AND TMD2.STATUS = " + Constant.STATUS_ENABLE + "\n");  
		sql.append("   AND TMD1.DEALER_ID IN (" + dealerId + ")\n");
		
		return dao.pageQuery(sql.toString(), null, getFunName()) ;
	}
	
	/**
	 * 获取当前经销商的下级经销商
	 * @param dealerId
	 * @return
	 */
	public String getDel__(String dealerId) {
		StringBuffer sql = new StringBuffer("\n") ;
		
		sql.append("SELECT TMD2.DEALER_ID, TMD2.DEALER_NAME\n");
		sql.append("  FROM TM_DEALER TMD1, TM_DEALER TMD2\n");  
		sql.append(" WHERE TMD1.DEALER_ID = TMD2.PARENT_DEALER_D\n");  
		sql.append("   AND TMD1.STATUS = " + Constant.STATUS_ENABLE + "\n");  
		sql.append("   AND TMD2.STATUS = " + Constant.STATUS_ENABLE + "\n");  
		sql.append("   AND TMD1.DEALER_ID IN (" + dealerId + ")\n");
		
		List<Map<String, Object>> dealerList = dao.pageQuery(sql.toString(), null, getFunName()) ;
		
		StringBuffer dealer = new StringBuffer("") ;
		if (null != dealerList && dealerList.size() > 0) {
			int len = dealerList.size() ;
			
			for (int i=0; i<len; i++) {
				if ("".equals(dealer.toString())) {
					dealer.append(dealerList.get(i).get("DEALER_ID")) ;
				} else {
					dealer.append(",").append(dealerList.get(i).get("DEALER_ID")) ;
				}
			}
		}
		
		return dealer.toString() ;
	}
	
	/**
	 * 通过公司id获取该公司下的所有经销商
	 * @param comId
	 * @return
	 */
	public List<Map<String, Object>> getDelList(String comId) {
		StringBuffer sql = new StringBuffer("") ;
		
		sql.append("SELECT TMD.DEALER_ID, TMD.DEALER_CODE, TMD.DEALER_NAME\n");
		sql.append("FROM TM_DEALER TMD\n");  
		sql.append("WHERE TMD.COMPANY_ID = " + comId + "\n");
		
		return dao.pageQuery(sql.toString(), null, getFunName()) ;
	}
	
	/**
	 * 通过经销商id获取经销商所在的业务范围
	 * @param comId
	 * @return
	 */
	public List<Map<String, Object>> getAreaList(String delId) {
		StringBuffer sql = new StringBuffer("") ;
		
		sql.append("SELECT TMBA.AREA_ID, TMBA.AREA_CODE, TMBA.AREA_NAME\n");
		sql.append("  FROM TM_DEALER_BUSINESS_AREA TMDBA, TM_DEALER TMD, TM_BUSINESS_AREA TMBA\n");  
		sql.append(" WHERE 1 = 1\n");  
		sql.append("   AND TMD.DEALER_ID = TMDBA.DEALER_ID\n");  
		sql.append("   AND TMBA.AREA_ID = TMDBA.AREA_ID\n");  
		sql.append("   AND TMDBA.DEALER_ID = " + delId + "\n");
		
		return dao.pageQuery(sql.toString(), null, getFunName()) ;
	}
	
	/**
	 * 获得当前经销商所属区域
	 * @param delId
	 * @return
	 */
	public Long getOrgId(String delId) {
		Long orgId = new Long("1") ;
		StringBuffer sql = new StringBuffer("") ;
		
		sql.append("SELECT TMO.ORG_ID, TMO.ORG_CODE, TMO.ORG_NAME, TMDOR.DEALER_ID\n");
		sql.append("  FROM TM_ORG TMO, TM_DEALER_ORG_RELATION TMDOR\n");  
		sql.append(" WHERE TMO.ORG_ID = TMDOR.ORG_ID\n");  
		sql.append("   AND TMDOR.DEALER_ID IN (" + delId + ")\n");

		List<Map<String, Object>> listOrg = dao.pageQuery(sql.toString(), null, getFunName()) ;
		
		if(null != listOrg && listOrg.size() > 0) {
			orgId = Long.parseLong(listOrg.get(0).get("ORG_ID").toString()) ;
		}
		
		return orgId ;
	}
	/**
	 * 根据登录用户的POSE_ID判断是否有"当日销售看板"功能，如果有，返回1，否则返回0
	 * */
	public String isDayReport(Long poseId){
		String isReport = "0";
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT TF.FUNC_NAME\n");
		sql.append("  FROM TC_POSE      TP,\n");  
		sql.append("       TR_ROLE_POSE RP,\n");  
		sql.append("       TC_ROLE      TR,\n");  
		sql.append("       TR_ROLE_FUNC TRF,\n");  
		sql.append("       TC_FUNC      TF\n");  
		sql.append(" WHERE TP.POSE_ID = RP.POSE_ID\n");  
		sql.append("   AND RP.ROLE_ID = TR.ROLE_ID\n");  
		sql.append("   AND TR.ROLE_ID = TRF.ROLE_ID\n");  
		sql.append("   AND TRF.FUNC_ID = TF.FUNC_ID\n");  
		sql.append("   AND TP.POSE_ID = "+poseId+"\n");  
		sql.append("   AND TF.FUNC_NAME = '当日销售看板'\n");
		List<Map<String, Object>> list = dao.pageQuery(sql.toString(), null, getFunName()) ;
		if (null != list && list.size()>0) {
			isReport = "1";
		}
		return isReport;
	}
	
	public PageResult<Map<String,Object>> getDealerBS(String con,int pageSize,int curPage){
		StringBuffer sql = new StringBuffer("\n");
		sql.append("select d.dealer_id,d.dealer_code, d.dealer_name, t.status\n");
		sql.append("  from tm_dealer d, tt_as_dealer_type t\n");  
		sql.append(" where d.dealer_id = t.dealer_id\n");
		if(StringUtil.notNull(con))
			sql.append(con);
		sql.append(" order by d.dealer_code asc\n");
		return this.pageQuery(sql.toString(),null,this.getFunName(), pageSize, curPage);
	}
	//zhumingwei add by 2011-02-24
	public PageResult<Map<String, Object>> queryDealerInfoChange(String dealerId,String code,String status,String startDate,String endDate,int curPage,int pageSize) throws Exception{
		StringBuffer sql= new StringBuffer();
		sql.append("SELECT * from tm_dealer_change a where 1=1\n");
		if (Utility.testString(dealerId)) {
			sql.append("and a.dealer_id = '"+dealerId+"' \n");
		}
		if (Utility.testString(code)) {
			sql.append("and a.dealer_num like '"+code+"' \n");
		}
		if (Utility.testString(status)) {
			sql.append("and a.status = "+status+" \n");
		}
		if(Utility.testString(startDate)){
			sql.append("AND A.dealer_time >= TO_DATE('"+startDate+"','YYYY-MM-DD HH24:MI:SS')\n" );
		}
		if(Utility.testString(endDate)){
			sql.append("AND A.dealer_time <= TO_DATE('"+endDate+"','YYYY-MM-DD HH24:MI:SS')\n" );
		}
		sql.append("order by a.dealer_time desc\n" );
		PageResult<Map<String, Object>> rs = dao.pageQuery(sql.toString(), null, getFunName(),pageSize, curPage);
		return rs;
	}
	
	public PageResult<Map<String, Object>> queryAuthDealerChange(String code,String status,String startDate,String endDate,int curPage,int pageSize) throws Exception{
		StringBuffer sql= new StringBuffer();
		sql.append("SELECT * from tm_dealer_change a where 1=1\n");
		if (Utility.testString(code)) {
			sql.append("and a.dealer_num like '"+code+"' \n");
		}
		if (Utility.testString(status)) {
			sql.append("and a.status = "+status+" \n");
		}
		if(Utility.testString(startDate)){
			sql.append("AND A.dealer_time >= TO_DATE('"+startDate+"','YYYY-MM-DD HH24:MI:SS')\n" );
		}
		if(Utility.testString(endDate)){
			sql.append("AND A.dealer_time <= TO_DATE('"+endDate+"','YYYY-MM-DD HH24:MI:SS')\n" );
		}
		PageResult<Map<String, Object>> rs = dao.pageQuery(sql.toString(), null, getFunName(),pageSize, curPage);
		return rs;
	}
	
	public List<Map<String, Object>> selectDealerInfo(long dealerId) {
		StringBuffer sql = new StringBuffer("") ;
		sql.append("select b.root_org_name,b.root_org_code,c.company_shortname,a.*,(select ee.dealer_name from  tm_dealer ee where ee.dealer_id=a.parent_dealer_d) as parent_name from tm_dealer a,vw_org_dealer_service b,TM_COMPANY c where a.dealer_id=b.dealer_id and a.company_id=c.company_id and a.dealer_id="+dealerId+"\n");
		return dao.pageQuery(sql.toString(), null, getFunName()) ;
	}
}
